package org.flexlancer.databases
{
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.events.EventDispatcher;
	import flash.events.IEventDispatcher;
	import flash.filesystem.File;
	
	import mx.controls.Alert;

	public class DatabaseManager extends EventDispatcher
	{
		private static var _singleton:DatabaseManager;
		private var _sqlConn:SQLConnection;
		private var _database_filename:String;
		
		public function DatabaseManager(target:IEventDispatcher=null,database:String = "")
		{
			super(target);
			this._database_filename = database;
			var databaseFile:File = File.applicationDirectory.resolvePath(this._database_filename);
			this._sqlConn = new SQLConnection();
			this._sqlConn.open(databaseFile);
			this.InitSchema();
		}
		
		public static function get Manager():DatabaseManager{
			try{
				if ( DatabaseManager._singleton == null ){
					DatabaseManager._singleton = new DatabaseManager(null,"easyhelper.db");
				}
			}catch(err:Error){
				throw err;
			}
			return DatabaseManager._singleton; 
		}

		protected function ProcessQuery(query:String):Array{
			try{
				var stm:SQLStatement = new SQLStatement();
				stm.text = query;
				stm.sqlConnection = this._sqlConn;
				stm.execute();
				var sqlRes:SQLResult = stm.getResult();
				if ( sqlRes == null || sqlRes.data == null) return [];
				else return sqlRes.data;
			}catch(err:Error){
				throw err;
			}
			return [];
		}
				
		private function CreateTable(tableName:String,tableFields:Array):void{
			try{
				if ( tableName == "" || tableFields.length == 0 ){
					return;
				}
				this.ProcessQuery( "CREATE TABLE IF NOT EXISTS " + tableName + "(" + tableFields.join(",") + ")");
			}catch(err:Error){
				throw err;
			}
		}

		public function Insert(tableName:String,nameValue:Object):void{
			try{
				var query:String = "INSERT INTO "+tableName;
				var names:String = "";
				var values:String = "";
				for ( var singleName:String in nameValue ){
					if ( names != "" ) names += ",";
					if ( values != "" ) values += ",";
					names += singleName;
					values += nameValue[singleName];
				}
				query += "("+names+") VALUES ("+values+")";
				//Alert.show(query,"Insert Query");
				this.ProcessQuery(query);
			}catch(err:Error){
				throw err;
			}
		}
		
		public function Select(tableName:String, valueFields:Array = null,
		condition:String = "", orderby:String = ""):Array{
			try{
				var query:String = "SELECT ";
				if ( valueFields == null ){
					query += "*";
				}else{
					query += valueFields.join(",");
				}
				query += " FROM " + tableName;
				if ( condition != "" )query += " WHERE " + condition;
				if ( orderby != "" ) query += " ORDER BY " + orderby;
				
				return this.ProcessQuery(query);
			}catch(err:Error){
				throw err;
			}
			return [];
		}

		public function Update ( tableName:String , nameValue:Object , condition:String = "" ):void{
			try{
				var query:String = "";
				for ( var singleName:String in nameValue ){
					if ( query != "" ) query += ", ";
					query += singleName + "=" + nameValue[singleName];
				}
				query = "UPDATE " + tableName + " SET " + query;
				if ( condition != "" ) query += " WHERE " + condition;
				
				this.ProcessQuery(query);
			}catch(err:Error){
				throw err;
			}
		}
		
		public function Delete(tableName:String, condition:String = "" ):void{
			try{
				var query:String = "DELETE FROM "+tableName;
				if ( condition != "" ) query += " WHERE "+condition;
				
				this.ProcessQuery(query); 
			}catch(err:Error){
				throw err;
			}
		}
		
		public function Begin():void{
			try{
				this._sqlConn.begin();
			}catch(err:Error){
				throw err;
			}
		}
		
		public function get LastInsertRowID():Number{
			try{
				return this._sqlConn.lastInsertRowID;
			}catch(err:Error){
				throw err;
			}
			return 0;
		}
		
		public function Commit():void{
			try{
				this._sqlConn.commit();
			}catch(err:Error){
				throw err;
			}
		}

		public function Rollback():void{
			try{
				this._sqlConn.rollback();
			}catch(err:Error){
				throw err;
			}
		}
		
		private function InitSchema():void{
			try{
				//this.ProcessQuery("Drop table salesinvoiceitems");
				this.CreateTable("environmentvariables",["name TEXT PRIMARY KEY","value TEXT"]);
				this.CreateTable("users",["userid TEXT PRIMARY KEY","userpassword TEXT"]);
				
				this.CreateTable("customers",
											["customerid INTEGER PRIMARY KEY AUTOINCREMENT",
											"customername TEXT", 
											"customertype TEXT", 
											"customerloyalty TEXT", 
											"credit INTEGER",
											"regdate TEXT",
											"numvehicles INTEGER"]);
											
				this.CreateTable("contactinfo",["contactinfoid INTEGER PRIMARY KEY AUTOINCREMENT","refid TEXT","contacttype TEXT", "contactdetail TEXT"]);
				this.CreateTable("vehicleinfo",["vehicleinfoid INTEGER PRIMARY KEY AUTOINCREMENT","refid TEXT","vehicletype TEXT", "vehicledetail TEXT","regnum TEXT"]);
				
				this.CreateTable("alerttypes",["type TEXT PRIMARY KEY "]);
				this.CreateTable("alertactions",["action TEXT PRIMARY KEY"]);
				this.CreateTable("alerts",["alertid INTEGER PRIMARY KEY AUTOINCREMENT","refid TEXT","alerttype TEXT", "alertdate text","alertaction TEXT","isactive INTEGER"]);

				this.CreateTable("vehicletree",["id INTEGER PRIMARY KEY AUTOINCREMENT","name TEXT","parentid INTEGER"]);
				
				this.CreateTable("productcategory",["catid INTEGER PRIMARY KEY AUTOINCREMENT","catname TEXT" ,"parentid INTEGER" ,"products INTEGER"]);
				this.CreateTable("quantityunit",["qunitid INTEGER PRIMARY KEY AUTOINCREMENT","qname TEXT"]);
				this.CreateTable("productprice",["productpriceid INTEGER PRIMARY KEY AUTOINCREMENT","refid TEXT","title TEXT", "price INTEGER", "markup INTEGER"]);
				this.CreateTable("products",["productid INTEGER PRIMARY KEY AUTOINCREMENT", "title TEXT", "barcode TEXT", "desc TEXT", "instock INTEGER", "orderlevel INTEGER", "cost INTEGER", "refproducttype text", "refsupplier text", "refunit text" ]);
								
				this.CreateTable("supplier",["supplierid INTEGER PRIMARY KEY AUTOINCREMENT","suppliername TEXT"]);
				this.CreateTable("supplierinfo",["supplierinfoid INTEGER PRIMARY KEY AUTOINCREMENT","refid TEXT","contacttype TEXT", "contactdetail TEXT"]);
				
				this.CreateTable("services",["serviceid INTEGER PRIMARY KEY AUTOINCREMENT","servicename TEXT"]);
				this.CreateTable("serviceprice",["servicepriceid INTEGER PRIMARY KEY AUTOINCREMENT","refid TEXT","pricetype TEXT", "price INTEGER"]);
				
				this.CreateTable("salesinvoice",
												["invoiceid INTEGER PRIMARY KEY AUTOINCREMENT",
												"refcustomer INTEGER",
												"salesdate TEXT",
												"salesnote TEXT",
												"itemscount INTEGER",
												"previouscredit INTEGER",
												"currenttotal INTEGER",
												"discount INTEGER",
												"totalpayable INTEGER",
												"amountpaid INTEGER",
												"balance INTEGER",
												"mileage INTEGER",
												"nextmileage INTEGER",
												"regnum TEXT",
												"keepbalance INTEGER"]
								);
				this.CreateTable("salesinvoiceitems",["itemsid INTEGER PRIMARY KEY AUTOINCREMENT","refid TEXT","refitem INTEGER","price INTEGER","quantity INTEGER","total INTEGER","reftype TEXT"]);
			}catch(err:Error){
				throw err;
			}
		} 	
	}
}//
/*
1) Quantity of each item in invoice should be editable. use following use case
	for edit quantity: 
	a) click on item in grid, which will populates in editable row
	b) press "INSERT" key or click on QTY field, and update quantity
	c) press 'Enter' to confirm quantity.
	
	for delete: DONE
	a) click on an item
	b) press 'DELETE' key from keyboard.

2) Update QTY from instock items.
*/